﻿--Person-----------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Person]'))
DROP TABLE [Person]
GO
CREATE TABLE [Person](
	[Key] [uniqueidentifier] NOT NULL DEFAULT NEWID(),
	[ParentPersonKey] [uniqueidentifier] NULL,
	[RelationshipKey] [uniqueidentifier] NULL,
	[PersonName] [nvarchar](MAX) NOT NULL,
	[Gender] [int] NOT NULL DEFAULT 0,
	[Birthday] [datetime] NULL,
	[Constellation] [nvarchar](3) NULL,
	[Blood] [nvarchar](10) NULL,
	[RelationshipWeight] [int] NOT NULL,
	[Description] [nvarchar](200) NULL,
	[CreatedStamp] [datetime] NOT NULL,
	[LastUpdatedStamp] [datetime] NOT NULL,
 CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED ([Key] ASC)) ON [PRIMARY]
GO
--PersonRelationship-----------------
IF  EXISTS (SELECT * FROM sysobjects WHERE name = 'PersonRelationship')
DROP TABLE [PersonRelationship]
GO
CREATE TABLE [PersonRelationship](
	[Key] [uniqueidentifier] NOT NULL DEFAULT NEWID(),
	[ParentKey] [uniqueidentifier] NULL,
	[RelationName] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_PersonRelationship] PRIMARY KEY CLUSTERED ([Key] ASC)) ON [PRIMARY]
GO
--PersonRelationship-----------------
IF  EXISTS (SELECT * FROM sysobjects WHERE name = 'Mobile')
DROP TABLE [PM_Mobile]
GO
CREATE TABLE [PM_Mobile](
	[MobileKey] [uniqueidentifier] NOT NULL,
	[PersonKey] [uniqueidentifier] NOT NULL,
	[MobileNumber] [varchar](15) NOT NULL,
	[CountryKey] [uniqueidentifier] NOT NULL,
	[MessagePrefix] [varchar](10) NULL,
	[Enabed] [bit] NOT NULL,
	[Description] [nvarchar](100) NULL,
	[InsertedStamp] [datetime] NOT NULL,
	[LastUpdatedStamp] [datetime] NOT NULL,
 CONSTRAINT [PK_PM_Mobile] PRIMARY KEY CLUSTERED ([MobileKey] ASC)) ON [PRIMARY]
GO
-------------------------------------------------
IF  EXISTS (SELECT * FROM sysobjects WHERE name = 'PM_Dwelling')
DROP TABLE [PM_Dwelling]
GO
CREATE TABLE [PM_Dwelling](
	[DwellingKey] [uniqueidentifier] NOT NULL,
	[LocationKey] [uniqueidentifier] NOT NULL,
	[Description] [nvarchar](100) NULL,
	[InsertedStamp] [datetime] NOT NULL,
	[LastUpdatedStamp] [datetime] NOT NULL,
 CONSTRAINT [PK_PM_Dwelling] PRIMARY KEY CLUSTERED ([DwellingKey] ASC)) ON [PRIMARY]
GO
-------------------------------------------------
IF  EXISTS (SELECT * FROM sysobjects WHERE name = 'PM_Person_Dwelling')
DROP TABLE [PM_Person_Dwelling]
GO
CREATE TABLE [PM_Person_Dwelling](
	[DwellingKey] [uniqueidentifier] NOT NULL,
	[PersonKey] [uniqueidentifier] NOT NULL,
	[Enabled] [bit] NOT NULL,
	[InsertedStamp] [datetime] NOT NULL,
	[LastUpdatedStamp] [datetime] NOT NULL,
 CONSTRAINT [PK_PM_Person_Dwelling] PRIMARY KEY CLUSTERED ([DwellingKey] ASC,[PersonKey] ASC)) ON [PRIMARY]
GO
-------------------------------------------------
IF  EXISTS (SELECT * FROM sysobjects WHERE name = 'PM_Tel')
DROP TABLE [PM_Tel]
GO
CREATE TABLE [PM_Tel](
	[TelKey] [uniqueidentifier] NOT NULL,
	[TelNumber] [varchar](15) NOT NULL,
	[LocationKey] [uniqueidentifier] NOT NULL,
	[Enabed] [bit] NOT NULL,
	[Description] [nvarchar](100) NULL,
	[InsertedStamp] [datetime] NOT NULL,
	[LastUpdatedStamp] [datetime] NOT NULL,
 CONSTRAINT [PK_PM_Tel] PRIMARY KEY CLUSTERED ([TelKey] ASC)) ON [PRIMARY]
GO

-------------------------------------------------
IF  EXISTS (SELECT * FROM sysobjects WHERE name = 'PM_PersonExtraData')
DROP TABLE [PM_PersonExtraData]
GO
CREATE TABLE [PM_PersonExtraData](
	[PersonKey] [uniqueidentifier] NOT NULL,
	[ExtraData] [xml] NOT NULL,
	[InsertedStamp] [datetime] NOT NULL,
	[LastUpdatedStamp] [datetime] NOT NULL,
 CONSTRAINT [PK_PM_PersonExtraData] PRIMARY KEY CLUSTERED ([PersonKey] ASC)) ON [PRIMARY]
GO


